-- 创建序列
CREATE SEQUENCE SEQ_USER_ID START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE SEQ_BOOK_ID START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE SEQ_LIST_ID START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE SEQ_DOWNLOAD_ID START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE SEQ_UPLOAD_ID START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE SEQ_COMMENT_ID START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE SEQ_CATEGORY_ID START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE SEQ_REQUEST_ID START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE SEQ_ACTIVITY_ID START WITH 1 INCREMENT BY 1;

-- 用户表
CREATE TABLE USERS (
                       USER_ID NUMBER PRIMARY KEY,
                       EMAIL VARCHAR2(255) UNIQUE NOT NULL,
                       PASSWORD_HASH VARCHAR2(255) NOT NULL,
                       NICKNAME VARCHAR2(100) UNIQUE NOT NULL,
                       ACCOUNT_LEVEL VARCHAR2(20) DEFAULT '初级' CHECK (ACCOUNT_LEVEL IN ('初级', '高级')),
                       DAILY_DOWNLOAD_LIMIT NUMBER DEFAULT 10,
                       AVATAR BLOB,
                       PRIVACY_STATUS VARCHAR2(10) DEFAULT '公开' CHECK (PRIVACY_STATUS IN ('公开', '私密')),
                       CREATED_AT TIMESTAMP DEFAULT SYSTIMESTAMP,
                       LAST_LOGIN TIMESTAMP
);

-- 用户表触发器
CREATE OR REPLACE TRIGGER TRG_USERS_BI
    BEFORE INSERT ON USERS
    FOR EACH ROW
BEGIN
    SELECT SEQ_USER_ID.NEXTVAL
    INTO :NEW.USER_ID
    FROM DUAL;
END;
/

-- 书籍表
CREATE TABLE BOOKS (
                       BOOK_ID NUMBER PRIMARY KEY,
                       TITLE VARCHAR2(255) NOT NULL,
                       AUTHOR VARCHAR2(255) NOT NULL,
                       ISBN10 VARCHAR2(10),
                       ISBN13 VARCHAR2(13),
                       PUBLISHER VARCHAR2(100),
                       PUB_YEAR NUMBER(4),
                       LANGUAGE VARCHAR2(50),
                       FILE_FORMAT VARCHAR2(10) CHECK (FILE_FORMAT IN ('EPUB', 'MOBI', 'PDF')),
                       FILE_SIZE_MB NUMBER(10,2),
                       COVER_IMAGE BLOB,
                       UPLOADER_ID NUMBER,
                       STATUS VARCHAR2(20) DEFAULT '待审核' CHECK (STATUS IN ('上架', '下架', '待审核')),
                       POPULARITY_SCORE NUMBER DEFAULT 0,
                       CREATED_AT TIMESTAMP DEFAULT SYSTIMESTAMP
);

-- 书籍表触发器
CREATE OR REPLACE TRIGGER TRG_BOOKS_BI
    BEFORE INSERT ON BOOKS
    FOR EACH ROW
BEGIN
    SELECT SEQ_BOOK_ID.NEXTVAL
    INTO :NEW.BOOK_ID
    FROM DUAL;
END;
/

-- 书单表
CREATE TABLE BOOK_LISTS (
                            LIST_ID NUMBER PRIMARY KEY,
                            LIST_NAME VARCHAR2(255) NOT NULL,
                            DESCRIPTION CLOB,
                            CREATOR_ID NUMBER,
                            COLOR_TAG VARCHAR2(20),
                            IS_PRIVATE VARCHAR2(1) DEFAULT 'N' CHECK (IS_PRIVATE IN ('Y', 'N')),
                            VIEW_COUNT NUMBER DEFAULT 0,
                            FAVORITE_COUNT NUMBER DEFAULT 0,
                            CREATED_AT TIMESTAMP DEFAULT SYSTIMESTAMP
);

-- 书单表触发器
CREATE OR REPLACE TRIGGER TRG_BOOK_LISTS_BI
    BEFORE INSERT ON BOOK_LISTS
    FOR EACH ROW
BEGIN
    SELECT SEQ_LIST_ID.NEXTVAL
    INTO :NEW.LIST_ID
    FROM DUAL;
END;
/

-- 书单-书籍关系表
CREATE TABLE LIST_BOOK_REL (
                               LIST_ID NUMBER,
                               BOOK_ID NUMBER,
                               ADDED_AT TIMESTAMP DEFAULT SYSTIMESTAMP,
                               PRIMARY KEY (LIST_ID, BOOK_ID)
);

-- 下载记录表
CREATE TABLE DOWNLOAD_HISTORY (
                                  DOWNLOAD_ID NUMBER PRIMARY KEY,
                                  USER_ID NUMBER,
                                  BOOK_ID NUMBER,
                                  DOWNLOAD_FORMAT VARCHAR2(10) CHECK (DOWNLOAD_FORMAT IN ('EPUB', 'MOBI', 'PDF')),
                                  DOWNLOAD_TIME TIMESTAMP DEFAULT SYSTIMESTAMP
);

-- 下载记录表触发器
CREATE OR REPLACE TRIGGER TRG_DOWNLOAD_HISTORY_BI
    BEFORE INSERT ON DOWNLOAD_HISTORY
    FOR EACH ROW
BEGIN
    SELECT SEQ_DOWNLOAD_ID.NEXTVAL
    INTO :NEW.DOWNLOAD_ID
    FROM DUAL;
END;
/

-- 上传记录表
CREATE TABLE UPLOAD_HISTORY (
                                UPLOAD_ID NUMBER PRIMARY KEY,
                                USER_ID NUMBER,
                                BOOK_ID NUMBER,
                                STATUS VARCHAR2(20) DEFAULT '待审核' CHECK (STATUS IN ('通过', '拒绝', '待审核')),
                                UPLOAD_TIME TIMESTAMP DEFAULT SYSTIMESTAMP
);

-- 上传记录表触发器
CREATE OR REPLACE TRIGGER TRG_UPLOAD_HISTORY_BI
    BEFORE INSERT ON UPLOAD_HISTORY
    FOR EACH ROW
BEGIN
    SELECT SEQ_UPLOAD_ID.NEXTVAL
    INTO :NEW.UPLOAD_ID
    FROM DUAL;
END;
/

-- 书籍修改记录表
CREATE TABLE BOOK_MODIFY_HISTORY (
                                     MODIFY_ID NUMBER PRIMARY KEY,
                                     BOOK_ID NUMBER,
                                     USER_ID NUMBER,
                                     TITLE_OLD VARCHAR2(255),
                                     TITLE_NEW VARCHAR2(255),
                                     AUTHOR_OLD VARCHAR2(255),
                                     AUTHOR_NEW VARCHAR2(255),
                                     ISBN10_OLD VARCHAR2(10),
                                     ISBN10_NEW VARCHAR2(10),
                                     ISBN13_OLD VARCHAR2(13),
                                     ISBN13_NEW VARCHAR2(13),
                                     PUBLISHER_OLD VARCHAR2(100),
                                     PUBLISHER_NEW VARCHAR2(100),
                                     PUB_YEAR_OLD NUMBER(4),
                                     PUB_YEAR_NEW NUMBER(4),
                                     LANGUAGE_OLD VARCHAR2(50),
                                     LANGUAGE_NEW VARCHAR2(50),
                                     MODIFY_REASON VARCHAR2(500),
                                     STATUS VARCHAR2(20) DEFAULT '待审核' CHECK (STATUS IN ('通过', '拒绝', '待审核')),
                                     CREATED_AT TIMESTAMP DEFAULT SYSTIMESTAMP
);

-- 评论表
CREATE TABLE COMMENTS (
                          COMMENT_ID NUMBER PRIMARY KEY,
                          USER_ID NUMBER,
                          BOOK_ID NUMBER,
                          LIST_ID NUMBER,
                          CONTENT CLOB NOT NULL,
                          LIKES NUMBER DEFAULT 0,
                          CREATED_AT TIMESTAMP DEFAULT SYSTIMESTAMP,
                          PARENT_COMMENT_ID NUMBER
);

-- 评论表触发器
CREATE OR REPLACE TRIGGER TRG_COMMENTS_BI
    BEFORE INSERT ON COMMENTS
    FOR EACH ROW
BEGIN
    SELECT SEQ_COMMENT_ID.NEXTVAL
    INTO :NEW.COMMENT_ID
    FROM DUAL;
END;
/

-- 评论点赞表
CREATE TABLE COMMENT_LIKES (
                               LIKE_ID NUMBER PRIMARY KEY,
                               COMMENT_ID NUMBER,
                               USER_ID NUMBER,
                               CREATED_AT TIMESTAMP DEFAULT SYSTIMESTAMP,
                               CONSTRAINT UK_COMMENT_USER UNIQUE (COMMENT_ID, USER_ID)
);

-- 评论点赞表序列
CREATE SEQUENCE SEQ_COMMENT_LIKE_ID START WITH 1 INCREMENT BY 1;

-- 评论点赞表触发器
CREATE OR REPLACE TRIGGER TRG_COMMENT_LIKES_BI
    BEFORE INSERT ON COMMENT_LIKES
    FOR EACH ROW
BEGIN
    SELECT SEQ_COMMENT_LIKE_ID.NEXTVAL
    INTO :NEW.LIKE_ID
    FROM DUAL;
END;
/

-- 分类表
CREATE TABLE CATEGORIES (
                            CATEGORY_ID NUMBER PRIMARY KEY,
                            CATEGORY_NAME VARCHAR2(100) UNIQUE NOT NULL
);

DROP TABLE CATEGORIES;
-- 分类表触发器
CREATE OR REPLACE TRIGGER TRG_CATEGORIES_BI
    BEFORE INSERT ON CATEGORIES
    FOR EACH ROW
BEGIN
    SELECT SEQ_CATEGORY_ID.NEXTVAL
    INTO :NEW.CATEGORY_ID
    FROM DUAL;
END;
/

-- 书籍-分类关系表
CREATE TABLE BOOK_CATEGORY_REL (
                                   BOOK_ID NUMBER,
                                   CATEGORY_ID NUMBER,
                                   PRIMARY KEY (BOOK_ID, CATEGORY_ID)
);

-- 书籍请求表
CREATE TABLE BOOK_REQUESTS (
                               REQUEST_ID NUMBER PRIMARY KEY,
                               BOOK_TITLE VARCHAR2(255) NOT NULL,
                               AUTHOR VARCHAR2(255),
                               ISBN VARCHAR2(13),
                               PUBLISHER VARCHAR2(100),
                               PUB_YEAR NUMBER(4),
                               LANGUAGE VARCHAR2(50),
                               COVER_IMAGE BLOB,
                               REQUESTER_ID NUMBER,
                               WANT_COUNT NUMBER DEFAULT 1,
                               STATUS VARCHAR2(20) DEFAULT '待上传' CHECK (STATUS IN ('待上传', '待审核', '完成')),
                               CREATED_AT TIMESTAMP DEFAULT SYSTIMESTAMP,
                               RELATED_BOOK_ID NUMBER
);

-- 书籍请求表触发器
CREATE OR REPLACE TRIGGER TRG_BOOK_REQUESTS_BI
    BEFORE INSERT ON BOOK_REQUESTS
    FOR EACH ROW
BEGIN
    SELECT SEQ_REQUEST_ID.NEXTVAL
    INTO :NEW.REQUEST_ID
    FROM DUAL;
END;
/

-- 用户请求关系表
CREATE TABLE USER_REQUEST_REL (
                                  USER_ID NUMBER,
                                  REQUEST_ID NUMBER,
                                  CREATED_AT TIMESTAMP DEFAULT SYSTIMESTAMP,
                                  PRIMARY KEY (USER_ID, REQUEST_ID)
);

-- 用户活跃度表
CREATE TABLE USER_ACTIVITY (
                               ACTIVITY_ID NUMBER PRIMARY KEY,
                               USER_ID NUMBER,
                               UPLOAD_SCORE NUMBER DEFAULT 0,
                               EDIT_SCORE NUMBER DEFAULT 0,
                               DOWNLOAD_SCORE NUMBER DEFAULT 0,
                               READ_SCORE NUMBER DEFAULT 0,
                               COMMENT_SCORE NUMBER DEFAULT 0,
                               TOTAL_SCORE NUMBER DEFAULT 0,
                               LAST_UPDATE TIMESTAMP DEFAULT SYSTIMESTAMP
);

-- 用户活跃度表触发器
CREATE OR REPLACE TRIGGER TRG_USER_ACTIVITY_BI
    BEFORE INSERT ON USER_ACTIVITY
    FOR EACH ROW
BEGIN
    SELECT SEQ_ACTIVITY_ID.NEXTVAL
    INTO :NEW.ACTIVITY_ID
    FROM DUAL;
END;
/

-- 积分历史表
CREATE TABLE SCORE_HISTORY (
                               HISTORY_ID NUMBER PRIMARY KEY,
                               USER_ID NUMBER,
                               TYPE VARCHAR2(20) NOT NULL,
                               SCORE NUMBER NOT NULL,
                               DESCRIPTION VARCHAR2(255),
                               CREATED_AT TIMESTAMP DEFAULT SYSTIMESTAMP
);

-- 其他对象保持不变...

-- 创建用户积分自动更新触发器
CREATE OR REPLACE TRIGGER TRG_UPDATE_SCORE
AFTER INSERT ON UPLOAD_HISTORY
FOR EACH ROW
BEGIN
  IF :NEW.STATUS = '通过' THEN
    UPDATE USER_ACTIVITY 
    SET UPLOAD_SCORE = UPLOAD_SCORE + 5,
        TOTAL_SCORE = TOTAL_SCORE + 5
    WHERE USER_ID = :NEW.USER_ID;
    
    -- 插入积分历史记录
    INSERT INTO SCORE_HISTORY (USER_ID, TYPE, SCORE, DESCRIPTION)
    VALUES (:NEW.USER_ID, '上传', 5, '书籍上传通过审核');
  END IF;
END;
/

-- 创建索引
CREATE INDEX IDX_USERS_EMAIL ON USERS (EMAIL);
CREATE INDEX IDX_USERS_NICKNAME ON USERS (NICKNAME);
CREATE INDEX IDX_BOOKS_TITLE ON BOOKS (TITLE);
CREATE INDEX IDX_BOOKS_ISBN ON BOOKS (ISBN10, ISBN13);
CREATE INDEX IDX_BOOKS_AUTHOR ON BOOKS (AUTHOR);
CREATE INDEX IDX_BOOKS_PUBLISHER ON BOOKS (PUBLISHER);
CREATE INDEX IDX_BOOKS_LANGUAGE ON BOOKS (LANGUAGE);
CREATE INDEX IDX_BOOKS_STATUS ON BOOKS (STATUS);
CREATE INDEX IDX_BOOKS_POPULARITY ON BOOKS (POPULARITY_SCORE DESC);
CREATE INDEX IDX_BOOK_LISTS_CREATOR ON BOOK_LISTS (CREATOR_ID);
CREATE INDEX IDX_DOWNLOAD_HISTORY_USER ON DOWNLOAD_HISTORY (USER_ID);
CREATE INDEX IDX_DOWNLOAD_HISTORY_BOOK ON DOWNLOAD_HISTORY (BOOK_ID);
CREATE INDEX IDX_DOWNLOAD_HISTORY_TIME ON DOWNLOAD_HISTORY (DOWNLOAD_TIME);
CREATE INDEX IDX_UPLOAD_HISTORY_USER ON UPLOAD_HISTORY (USER_ID);
CREATE INDEX IDX_UPLOAD_HISTORY_STATUS ON UPLOAD_HISTORY (STATUS);
CREATE INDEX IDX_COMMENTS_BOOK ON COMMENTS (BOOK_ID);
CREATE INDEX IDX_COMMENTS_USER ON COMMENTS (USER_ID);
CREATE INDEX IDX_BOOK_REQUESTS_STATUS ON BOOK_REQUESTS (STATUS);

-- 创建用户活跃度排行榜视图
CREATE OR REPLACE VIEW USER_RANKING AS
SELECT 
  U.USER_ID,
  U.NICKNAME,
  UA.TOTAL_SCORE
FROM USERS U
JOIN USER_ACTIVITY UA ON U.USER_ID = UA.USER_ID
ORDER BY UA.TOTAL_SCORE DESC;

